﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.IO;
using System.Data;

namespace Mimi.Common
{
    public class SqliteWrapper
    {
        #region Fields
        private SQLiteConnection sqliteConnection;
        private string sqliteVersion;
        #endregion

        #region Constants
        private const string c_sqliteExtension = ".sqlite";
        private const string c_sqliteConnectionFormat = "Data Source={0};Version={1};";
        private const string c_sqliteCreateTable = "create table {0} ({1})";
        private const string c_sqliteInsert = "insert into {0} ({1}) values ({2})";
        private const string c_SqlUpdateForm = "update {0} set {1} where {2}";
        private const string c_deleteFormat = "delete from {0} where {1}";
        #endregion

        #region Methods
        /// <summary>
        /// Default constructor
        /// </summary>
        public SqliteWrapper()
        {
            sqliteConnection = new SQLiteConnection();
            sqliteVersion = "3";
        }

        /// <summary>
        /// Create a database file with specified name on local
        /// </summary>
        /// <param name="databaseName"></param>
        public void CreateSqliteDatabase(string databaseName)
        {
            try
            {
                string filename = databaseName + c_sqliteExtension;

                if (!File.Exists(filename))
                {
                    SQLiteConnection.CreateFile(filename);
                }
            }
            catch
            {
                //Debug code goes here
            }
        }

        /// <summary>
        /// Create a sqlite table
        /// </summary>
        public void CreateSqliteTable(string tableName, IEnumerable<string> columnList)
        {
            try
            {
                List<string> columns = new List<string>(columnList);

                string columnDeclaration = string.Empty;

                foreach (string column in columnList)
                {
                    columnDeclaration += column;
                    columnDeclaration += ",";
                }

                if (columnDeclaration.Length > 0)
                {
                    columnDeclaration = columnDeclaration.Remove(columnDeclaration.Length - 1, 1);

                    string commandText = string.Format(c_sqliteCreateTable, tableName, columnDeclaration);

                    SQLiteCommand sqliteCommand = sqliteConnection.CreateCommand();
                    sqliteCommand.CommandText = commandText;
                    sqliteCommand.ExecuteNonQuery();
                }
            }
            catch
            {
                //Debug code goes here
            }
        }

        /// <summary>
        /// Insert record into table
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="recordList"></param>
        public void InsertRecordIntoTable(string tableName, IDictionary<string, string> recordData)
        {
            try
            {
                string columnList = string.Empty;
                string valueList = string.Empty;

                foreach (var item in recordData)
                {
                    columnList += item.Key;
                    columnList += ",";
                    valueList += "\'" + item.Value + "\'";
                    valueList += ",";
                }

                if (columnList.Length > 0 && valueList.Length > 0)
                {
                    columnList = columnList.Remove(columnList.Length - 1, 1);
                    valueList = valueList.Remove(valueList.Length - 1, 1);

                    string commandText = string.Format(c_sqliteInsert, tableName, columnList, valueList);

                    SQLiteCommand sqliteCommand = sqliteConnection.CreateCommand();
                    sqliteCommand.CommandText = commandText;
                    sqliteCommand.ExecuteNonQuery();
                }
            }
            catch
            {
                //Debug code goes here
            }
        }

        /// <summary>
        /// Query data in database
        /// </summary>
        /// <param name="query"></param>
        public DataTable QueryData(string query)
        {
            try
            {
                SQLiteCommand sqliteCommand = sqliteConnection.CreateCommand();

                sqliteCommand.CommandText = query;

                SQLiteDataAdapter adapter = new SQLiteDataAdapter(sqliteCommand);

                DataTable queryResult = new DataTable();

                adapter.Fill(queryResult);

                return queryResult;
            }
            catch
            {
                //Debug code goes here
                return null;
            }
        }

        /// <summary>
        /// Update record data with new value
        /// </summary>
        /// <param name="tableName">Table has record need to be updated</param>
        /// <param name="condition">Condition to find the record</param>
        /// <param name="newValues">new value</param>
        public bool UpdateRecordData(string tableName, string condition, IEnumerable<string> newValues)
        {
            try
            {
                string expression = string.Empty;

                foreach (string value in newValues)
                {
                    expression += value;
                    expression += ", ";
                }

                if (!string.IsNullOrEmpty(expression))
                {
                    expression = expression.Remove(expression.Length - 2, 2);
                }

                string query = string.Format(c_SqlUpdateForm, tableName, expression, condition);

                SQLiteCommand sqliteCommand = sqliteConnection.CreateCommand();
                sqliteCommand.CommandText = query;

                int rowCount = sqliteCommand.ExecuteNonQuery();

                return rowCount > 0;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Delete record from table
        /// </summary>
        public void DeleteRecord(string tableName, string condition)
        {
            try
            {
                string query = string.Format(c_deleteFormat, tableName, condition);

                SQLiteCommand sqliteCommand = sqliteConnection.CreateCommand();
                sqliteCommand.CommandText = query;

                sqliteCommand.ExecuteNonQuery();
            }
            catch
            {
                //Debug code goes here
            }
        }

        /// <summary>
        /// Create a connection to existent database file
        /// </summary>
        /// <param name="databaseName"></param>
        public void ConnectToSqliteDatabase(string databaseName)
        {
            try
            {
                string fileName = databaseName + c_sqliteExtension;
                string connectionString = string.Format(c_sqliteConnectionFormat, fileName, sqliteVersion);

                sqliteConnection.ConnectionString = connectionString;
                sqliteConnection.Open();
            }
            catch
            {
                //Debug code goes here
            }
        }

        /// <summary>
        /// Close database connection
        /// </summary>
        /// <param name="databaseName"></param>
        public void CloseDatabaseConnection()
        {
            sqliteConnection.Close();
        }
        #endregion
    }
}
